import random
import string
import uuid

import faker as faker
from faker import Faker

import DButils
import openpyxl

# wb = openpyxl.Workbook('user.xlsx')
# wb.create_sheet(title='msg_sheet')
# wb.save('user.xlsx')
from openpyxl import Workbook
from openpyxl.packaging import workbook

wb = openpyxl.load_workbook('user.xlsx')
# wb.create_sheet(title='sheetlist')
ws = wb['sheetlist']
# rows = ['id','idcard','username','realname','pwd','telphone','email','age','sex','address','hiredate','sal','job','company']
# print(rows)
# ws.append(rows)
# rows = []
# faker = Faker(locale='zh_CN')
# for i in range(2, 2002):
#     values =[random.randint(1000,9999)+i,random.randint(10000,99999)+i,''.join(random.choices('abcdefghijklmnopqrstuvwxyz',k=4)),faker.name(),'123456',faker.phone_number(),faker.email(),random.randint(1,100),random.choice('男女'),faker.address(),'2024-06-13',random.randint(1000,9999),'Java开发','tx']
#     rows.append(values)
#     # print(rows)
# for row in rows:
#     ws.append(row)

#读取所有行
# for row in ws.iter_rows():
#     for cell in row:
#         print(cell.value, end='\t\t')
#     print()
mr = ws.max_row
mc = ws.max_column
print(f'总共{mr},总共{mc}列')
sql = "INSERT INTO user_info (id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company) VALUES (%s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
for i in range(2,mr):
    row_values = [cell.value for cell in ws[i]]
    print(row_values)
    param = tuple(row_values)
    # print(param)
    print("..........")
    DButils.databases_operations(sql, param)
    # print("。。。。。。。。。。。")
wb.save('user.xlsx')
print('目录写入成功')
